Excel VBA vs Python xlwings： Rules for Writing Regular Expressions

Using regular expressions can accomplish more complex text searches and replacements. This section introduces the rules for writing regular expressions. This content is the core of regular expressions and is basically the same across different programming languages.

Metacharacters

Metacharacters are characters in regular expressions that have special meanings beyond their literal sense. For example, in Python regular expressions, \d represents a digit, and \s represents whitespace. Common metacharacters are listed in Table 8-2.

Table 8-2 Common Metacharacters

Metacharacter	Description	Metacharacter	Description
.	Matches any character except newline	^	Matches the start of a string
\w	Matches letters, digits, underscore, or Chinese characters	$	Matches the end of a string
\s	Matches any whitespace	\n	Matches a newline
\d	Matches a digit	\r	Matches a carriage return
\b	Matches the start or end of a word	\t	Matches a tab

Usually, we specify characters to find or search within a certain range, but sometimes the requirement is reversed — to exclude certain characters or search outside a character range. In such cases, we use metacharacters for negation, e.g., \D for non-digits, \S for non-whitespace. Common negating metacharacters are listed in Table 8-3.

Table 8-3 Common Negating Metacharacters

Negating Metacharacter	Description
\W	Matches any character that is not a letter, digit, underscore, or Chinese character
\S	Matches any non-whitespace character
\D	Matches any non-digit character
\B	Matches a position that is not the start or end of a word
[^x]	Matches any character except x
[^aeiou]	Matches any character except a, e, i, o, u

We now illustrate metacharacters with examples.

Given the original string "BC_101PW%", find the digits and replace them with an empty string (i.e., delete them).

【Excel VBA】

Sample file: Samples\ch20\Excel VBA\元字符.xlsm. The regular expression "\d" represents a single digit.

code.vba

Sub Sam01()
    Dim objReg As New RegExp
    Dim strT As String
    Dim mcT As MatchCollection
    Dim matT As Match
    strT = "BC_101PW%"
    With objReg
        .Global = True
        .Pattern = "\d"
        Set mcT = .Execute(strT)
        Debug.Print .Replace(strT, "")
    End With
    For Each matT In mcT
        If matT <> " " Then Debug.Print matT.Value
    Next matT
End Sub

Running the procedure prints the replaced and found results in the Immediate Window:

code.vba

BC_PW%
1
0
1

【Python】

In the Python shell:

code.python

>>> import re
>>> a = 'BC_101PW%'
>>> m0 = re.findall(r'\d', a)
>>> m0
['1', '0', '1']
>>> for i in m0:
... print(i)
... 
1
0
1
>>> ms = re.sub(r'\d', '', a)
>>> ms
'BC_PW%'

Next example tests the metacharacter \b (word boundary), matching strings where C is at the start of the string or preceded by a space, followed by a digit. Replace matches with empty.

【Excel VBA】

code.vba

Sub Sam02()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "C5dC56 C5"
    With objReg
        .Global = True
        .Pattern = "\bC\d"
        Debug.Print .Replace(strT, "")
    End With
End Sub

Output in Immediate Window:

code.vba

dC56

The first C5 is at the string start, satisfying C plus digit; the second C5 is preceded by a space, satisfying \b. Removing them leaves "dC56".

【Python】

code.python

>>> import re
>>> a = 'C5dC56 C5'
>>> m = re.sub(r'\bC\d', '', a)
>>> m
'dC56 '

The metacharacter ^ restricts matching to the very start of the string, e.g., ^\d means the string starts with a digit.

【Excel VBA】

code.vba

Sub Sam03()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "12345my09"
    With objReg
        .Global = True
        .Pattern = "^\d+"
        Set mcT = .Execute(strT)
        For Each matT In mcT
            Debug.Print matT
        Next matT
    End With
End Sub

Output:

code.vba

Only "12345" at the start matches; "09" is not at the start. + means “one or more” of the preceding element (\d).

【Python】

code.python

>>> import re
>>> a = '12345my09'
>>> m = re.findall(r'^\d+', a)
>>> for i in m:
... print(i)
... 
12345

\D indicates non-digit; $ forces match at end of string, e.g., C$ means last char is C.

【Excel VBA】

code.vba

Sub Sam04()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "m12345my09W"
    With objReg
        .Global = True
        .Pattern = "\d+\D$"
        Set mcT = .Execute(strT)
        For Each matT In mcT
            Debug.Print matT
        Next matT
    End With
End Sub

Output:

code.vba

09W

\d+\D$ matches digits followed by a non-digit at the string end.

【Python】

code.python

>>> import re
>>> a = 'm12345my09W'
>>> m = re.findall(r'\d+\D$', a)
>>> m
['09W']

Repetition

Sometimes we need to match multiple consecutive occurrences of a character type — this is repetition. The number of repetitions can be fixed or variable. In Python regex, \d+ means one or more digits (variable), \d{5} means exactly five digits (fixed).

Repetition metacharacters are listed in Table 8-4.

Table 8-4 Repetition Metacharacters

Metacharacter	Description	Metacharacter	Description
*	Repeat 0 or more times	{n}	Repeat exactly n times
+	Repeat 1 or more times	{n,}	Repeat n or more times
?	Repeat 0 or 1 time	{n,m}	Repeat between n and m times

* = {0,} (zero or more). Example: find substrings starting with W followed by zero or more digits.

【Excel VBA】

code.vba

Sub Sam08()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "W123YZW85CW0DFWU"
    With objReg
        .Global = True
        .Pattern = "W\d*"
        Set mcT = .Execute(strT)
        For Each matT In mcT
            Debug.Print matT
        Next matT
    End With
End Sub

Output:

code.vba

W123
W85
W0
W

Last match is just W (no digits after).

【Python】

code.python

>>> import re
>>> a = 'W123YZW85CW0DFWU'
>>> m = re.findall(r'W\d*', a)
>>> m
['W123', 'W85', 'W0', 'W']

+ = {1,} (one or more). Example: W followed by one or more digits.

【Excel VBA】

code.vba

Sub Sam081()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "W123YZW85CW0DFWU"
    With objReg
        .Global = True
        .Pattern = "W\d+"
        Set mcT = .Execute(strT)
        For Each matT In mcT
            Debug.Print matT
        Next matT
    End With
End Sub

Output:

code.vba

W123
W85
W0

【Python】

code.python

>>> import re
>>> a = 'W123YZW85CW0DFWU'
>>> m = re.findall(r'W\d+', a)
>>> m
['W123', 'W85', 'W0']

? = {0,1}. Example: numbers with optional decimal point.

【Excel VBA】

code.vba

Sub Sam09()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "W10.23RWA908C5..1"
    With objReg
        .Global = True
        .Pattern = "\d+\.?\d+"
        Set mcT = .Execute(strT)
        For Each matT In mcT
            Debug.Print matT
        Next matT
    End With
End Sub

Output:

code.vba

10.23
908

Fixed repetition {n}: match exactly n repeats.

【Excel VBA】

code.vba

Sub Sam10()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "WT123Pq89C"
    With objReg
        .Global = True
        .Pattern = "\d{3}"
        Debug.Print .Replace(strT, "")
    End With
End Sub

Output:

code.vba

WTPq89C

Range {n,m}: between n and m repeats.

【Excel VBA】

code.vba

Sub Sam11()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "WT123Pq89C"
    With objReg
        .Global = True
        .Pattern = "\d{2,3}"
        Debug.Print .Replace(strT, "")
    End With
End Sub

Output:

code.vba

WTPqC

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = 'WT123Pq89C'
>>> re.sub(r'\d{2,}', '', a)
'WTPqC'

Character Classes

The previous methods can be used to find specified digits, letters, or whitespace. However, if a character set is given and the characters to be found must be either within this set or outside it, we need to use square brackets. The ways to define character sets with square brackets are shown in Table 8-5.

Table 8-5 Ways to Define Character Sets with Square Brackets

Application Example	Description
[adwkf]	Find characters that are one of those inside the brackets
[^adwkf]	Find characters that are NOT any of those inside the brackets
[b-f]	Find characters that are one of b to f
[^b-f]	Find characters that are NOT any of b to f

Continued Table

Application Example	Description
[2-5]	Find characters that are one of 2 to 5
[2-46-9]	Find characters that are one of 2-4 or 6-9
[a-w2-5A-W]	Find characters that are one of a-w, 2-5, or A-W
[^一-龥] or [^\u4e00-\u9fa5]	Find non-Chinese characters (or characters not in the Chinese Unicode range)

A character set enclosed in square brackets matches any single character within it. If [](@ref) is used, it matches any character outside the set (not matching characters inside the brackets).

Below is a string—we will find characters that match any character in the brackets.

【Excel VBA】

The sample file path is Samples\ch20\Excel VBA\Character Classes.xlsm.

code.vba

Sub Sam15()
    Dim objReg As New RegExp   ' Create a regex object
    Dim strT As String
    strT = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"   ' Given string
    With objReg
        .Global = True
        .Pattern = "[AEIOU]"   ' Regex: Match any character in the brackets
        Debug.Print .Replace(strT, "")   ' Delete matched characters
    End With
End Sub
After running the procedure, the output in the Immediate Window is:
BCDFGHJKLMNPQRSTVWXYZ

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
>>> re.sub('[AEIOU]', '', a)
'BCDFGHJKLMNPQRSTVWXYZ'

Now, find characters that do NOT match any character in the brackets.

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Character Classes.xlsm.

code.vba

Sub Sam16()
    Dim objReg As New RegExp   ' Output regex object(note: original typo "输出" → "Create")
    Dim strT As String
    strT = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"   ' Given string
    With objReg
        .Global = True
        .Pattern = "[^AEIOU]"   ' Regex: Match characters NOT in the brackets
        Debug.Print .Replace(strT, "")   ' Delete matched characters
    End With
End Sub
Output in Immediate Window:
AEIOU

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
>>> re.sub(r'[^AEIOU]', '', a)
'AEIOU'

Given a string, find characters that fall within a specified range in the brackets.

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Character Classes.xlsm.

code.vba

Sub Sam17()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"   ' Given string
    With objReg
        .Global = True
        .Pattern = "[G-T]"   ' Regex: Match characters in the range G-T
        Debug.Print .Replace(strT, "")   ' Delete matched characters
    End With
End Sub
Output in Immediate Window:
ABCDEFUVWXYZ

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
>>> re.sub(r'[G-T]', '', a)
'ABCDEFUVWXYZ'

Find characters in the ranges 1–5 and G–T from a string.

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Character Classes.xlsm.

code.vba

Sub Sam18()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"   ' Given string
    With objReg
        .Global = True
        .Pattern = "[1-5G-T]"   ' Regex: Match characters in 1-5 or G-T
        Debug.Print .Replace(strT, "")   ' Delete matched characters
    End With
End Sub
Output in Immediate Window:
ABCDEFUVWXYZ67890

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'
>>> re.sub(r'[1-5G-T]', '', a)
'ABCDEFUVWXYZ67890'

When finding Chinese characters in a string, use square brackets to specify the Chinese character range. There are two ways: [一-龥] (using Chinese character order) and [\u4e00-\u9fa5] (using Unicode hexadecimal codes). The latter uses 4-digit hex numbers: "一" (the first Chinese character) has the code \u4e00, and the last common Chinese character has the code \u9fa5.

Below is a string containing Chinese characters—we will find the Chinese characters and replace them with an empty string.

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Character Classes.xlsm.

code.vba

Sub Sam06()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "123 中 hwo 文 tr89 字符"   ' Given string
    With objReg
        .Global = True
        .Pattern = "[\u4e00-\u9fa5]"   ' Regex: Specify Chinese range(alternative: "[一-龥]")
        Debug.Print .Replace(strT, "")   ' Delete matched Chinese characters
    End With
End Sub
Output in Immediate Window:
123 hwo tr89

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = '123 中 hwo 文 tr89 字符'
>>> m = re.findall('[\u4e00-\u9fa5]', a)
>>> m
['中', '文', '字', '符']
>>> m = re.sub('[\u4e00-\u9fa5]', '', a)
>>> m
'123  hwo  tr89 '
123  hwo  tr89

8.2.4 Branch Conditions

Suppose there are multiple rules—matching any one of them completes the match. This requires branch conditions, which use | to separate different rules. For example, if a number is followed by a mass unit (some records as "公斤", others as "千克"), use \d+(公斤|千克) to extract it (equivalent to \d+公斤|\d+千克).

Below is a string where numbers are followed by "公斤", "kg", or "千克"—use branch conditions to write a regex for extraction.

Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Metacharacters.xlsm.

code.vba

Sub Sam27()
    ' Omitted partial code...
    strT = "10公斤 20kg 30千克"   ' Given string
    With objReg
        .Global = True
        .Pattern = "\d+(公斤|千克|kg)"   ' Regex: Number followed by unit
        '.Pattern = "\d+公斤|\d+千克|\d+kg"   ' Equivalent writing
        Set mcT = .Execute(strT)   ' Execute search
        For Each matT In mcT   ' Traverse results
            Debug.Print matT   ' Output result
        Next
    End With
End Sub

Output in Immediate Window:

code.vba

10公斤
20kg
30千克

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = '10公斤 20kg 30千克'
>>> m = re.finditer(r'\d+(公斤|千克|kg)', a)
>>> for i in m:
    print(i.group(0))
10公斤
20kg
30千克

Capturing Groups and Non-Capturing Groups

Regular expressions may contain subexpressions (enclosed in parentheses), which are treated as a whole. For example, the regex "((ABC){2})" repeats "ABC" twice as a whole.

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Groups.xlsm.

code.vba

Sub Sam19()
    Dim objReg As New RegExp
    Dim strT As String
    strT = "ABCABCWTU238"   ' Given string
    With objReg
        .Global = True
        .Pattern = "((ABC){2})"   ' Regex: "ABC" repeated twice as a whole
        Debug.Print .Replace(strT, "")   ' Delete matched part
    End With
End Sub
Output in Immediate Window:
WTU238

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = 'ABCABCWTU238'
>>> m = re.search('((ABC){2})', a)
>>> re.sub('((ABC){2})', '', a)
'WTU238'

When using parentheses to group regex, group numbers are automatically assigned (left-to-right, outer-to-inner). Group numbers allow backreferences to the corresponding group.

In the code below, the regex r"(WT)\d+\1" matches substrings where the start and end are "WT" with one or more digits in between. Note: \1 refers to the "WT" in the first parenthesis (group 1).

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Groups.xlsm.

code.vba

Sub Sam20()
    ' Omitted partial code...
    strT = "abcWT12389WT"   ' Given string
    With objReg
        .Global = True
        .Pattern = "(WT)\d+\1"   ' Regex: Two "WT"s with digits in between
        Set mcT = .Execute(strT)   ' Execute search
        For Each matT In mcT   ' Traverse results
            Debug.Print matT   ' Output result
        Next
    End With
End Sub
Output in Immediate Window:
WT12389WT

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = 'abcWT12389WT'
>>> m = re.finditer(r'(WT)\d+\1', a)
>>> for i in m:
    print(i.group())
WT12389WT

The match "WT12389WT" starts and ends with "WT" and has only digits in between—meeting the requirement.

The example below shows more groups. The regex r"((WT){2})((PR){2})\d+\2\4" has 4 pairs of parentheses (two layers on the left, two on the right). Let’s explore the group numbers:

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Groups.xlsm.

code.vba

Sub Sam21()
    ' Omitted partial code...
    strT = "abWTWTPRPR123WTPR56"   ' Given string
    With objReg
        .Global = True
        .Pattern = "((WT){2})((PR){2})\d+\2\4"   ' Regex
        Set mcT = .Execute(strT)   ' Execute search
        For Each matT In mcT   ' Traverse results
            Debug.Print matT
            Debug.Print matT.SubMatches(0)   ' Output submatch of group 0
            Debug.Print matT.SubMatches(1)   ' Group 1
            Debug.Print matT.SubMatches(2)   ' Group 2
            Debug.Print matT.SubMatches(3)   ' Group 3
        Next
    End With
End Sub

Output in Immediate Window:

code.vba

WTWTPRPR123WTPR
WTWT
WT
PRPR
PR

The output shows group numbering follows left-to-right, outer-to-inner:

Left side: Outer parenthesis (WT){2} → group 1 ("WTWT"); inner parenthesis WT → group 2 ("WT").

Right side: Outer parenthesis (PR){2} → group 3 ("PRPR"); inner parenthesis PR → group 4 ("PR").

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = 'abWTWTPRPR123WTPR56'
>>> m = re.search(r'((WT){2})((PR){2})\d+\2\4', a)
>>> m.group(1)
'WTWT'
>>> m.group(2)
'WT'
>>> m.group(3)
'PRPR'
>>> m.group(4)
'PR'

Parentheses define capturing groups (saved in memory for later use). However, sometimes we don’t need to save the matched content—this is a non-capturing group. To create one, add ?: at the start of the parentheses (e.g., (?:\d{3})). Non-capturing groups do not get numbered or saved, saving memory and improving efficiency.

Below is a string with the regex r"(?:ab)(CD)\d+\1" (first group is non-capturing).

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Groups.xlsm.

code.vba

Sub Sam22()
    Dim objReg As New RegExp
    Dim strT As String, mc, c
    Dim m As Match
    strT = "abCD123CDbc"   ' Given string
    With objReg
        .Global = True
        .Pattern = "(?:ab)(CD)\d+\1"   ' Regex: First group is non-capturing
        Set mc = .Execute(strT)   ' Execute search
        For Each m In mc   ' Traverse results
            Debug.Print m.Value   ' Output matched value
            Debug.Print m.SubMatches(0)   ' Output first saved group
        Next
    End With
End Sub

Output in Immediate Window:

code.vba

abCD123CD
CD

Since the first group is non-capturing, it is not numbered. Thus, the first saved group is "CD" (not "ab").

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = 'abCD123CDbc'
>>> m = re.finditer(r'(?:ab)(CD)\d+\1', a)
>>> for i in m:
    print(i.group())
abCD123CD

Use re.finditer to get a match iterator and loop through results. The matched string includes "ab".

Use re.search to get a Match object and check its groups() attribute:

code.python

>>> m = re.search(r'(?:ab)(CD)\d+\1', a)
>>> m.groups()
('CD',)

Only one group is returned ("CD")—confirming the first group (non-capturing) is neither numbered nor saved.

Zero-Width Assertions

Zero-width assertions find content before or after a specified pattern (excluding the pattern itself). There are two types:

1. Zero-Width Positive Lookahead Assertion

Expression: (?=exp) — finds content before exp.

2. Zero-Width Positive Lookbehind Assertion

Expression: (?<=exp) — finds content after exp.

Combining both, we can find content between two patterns.

Below is a string—extract the number before "公斤" (unit: kilogram). Use zero-width positive lookahead.

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Zero-Width Assertions.xlsm.

code.vba

Sub Sam28()
    ' Omitted partial code...
    strT = "10公斤 20公斤 30公斤"   ' Given string
    With objReg
        .Global = True
        .Pattern = "\d+(?=公斤)"   ' Regex: Number before "公斤"(exclusive)
        Set mcT = .Execute(strT)   ' Execute search
        For Each matT In mcT   ' Traverse results
            Debug.Print matT   ' Output result
        Next
    End With
End Sub

Output in Immediate Window:

code.vba

10
20
30

The regex r"\d+(?=公斤)" matches numbers before "公斤" (excluding "公斤")—correct.

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = '10公斤 20公斤 30公斤'
>>> m = re.finditer(r'\d+(?=公斤)&#x27;, a)  # Extract only numbers before the unit
>>> for i in m:
    print(i.group())
10
20
30

Below is a string—extract names after titles like "同学" (classmate), "战友" (comrade-in-arms), or "师兄" (senior). Use zero-width positive lookbehind.

code.python

>>> import re
>>> a = '同学李海 战友王刚 师兄张三'
>>> m = re.finditer(r'(?<=同学|战友|师兄)\w+', a)  # Extract names after titles
>>> for i in m:
    print(i.group())
李海
王刚
张三

The regex r"(?<=同学|战友|师兄)\w+" matches substrings after the titles (excluding the titles). Excel VBA cannot perform this operation.

Negative Zero-Width Assertions

Negative zero-width assertions assert that a position does NOT match a specified pattern. There are two types:

1. Zero-Width Negative Lookahead Assertion

Expression: (?!exp) — asserts the next content does NOT match exp.

2. Zero-Width Negative Lookbehind Assertion

Expression: (?<!exp) — asserts the previous content does NOT match exp.

Below is a string—match "123" where:

The preceding character is a letter, digit, or underscore (\w).

The following character is NOT a capital letter ([A-Z]).

Use zero-width negative lookahead.

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Negative Zero-Width Assertions.xlsm.

code.vba

Sub Sam31()
    ' Match "123" where the preceding is \w and the following is NOT [A-Z]
    ' Omitted partial code...
    strT = "5123Wgh123hp123456"   ' Given string
    With objReg
        .Global = True
        .Pattern = "\w123(?![A-Z])"   ' Regex: \w + "123" + not followed by [A-Z]
        Set mcT = .Execute(strT)   ' Execute search
        For Each matT In mcT   ' Traverse results
            Debug.Print matT   ' Output result
        Next
    End With
End Sub

Output in Immediate Window:

code.vba

h123
p123

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = '5123Wgh123hp123456'
>>> m = re.finditer('\w123(?![A-Z])', a)
>>> for i in m:
    print(i.group())
h123
p123

The first "123" is followed by "W" (capital letter)—so it does not match.

Greediness vs. Laziness

Previously, * and + match as many characters as possible (greedy matching). Sometimes we need to match as few as possible (lazy matching). Add a ? after the greedy quantifier to make it lazy.

Common lazy matching formats are shown in Table 8-6.

Table 8-6 Common Lazy Matching Formats

Lazy Matching Format	Description
*?	Repeat any number of times, but as few as possible
+?	Repeat one or more times, but as few as possible
??	Repeat zero or one time, but as few as possible
{n,m}?	Repeat n to m times, but as few as possible
{n,}?	Repeat n or more times, but as few as possible

Below is a string—compare greedy and lazy matching results.

【Excel VBA】

Sample file path: Samples\ch20\Excel VBA\Greediness and Laziness.xlsm.

code.vba

Sub Sam32()
    ' Omitted partial code...
    strT = " 123  abc53  59wt "   ' Given string
    With objReg
        .Global = True
        .Pattern = "\s.+?\s"   ' Regex: Lazy matching(space + minimal chars + space)
        Set mcT = .Execute(strT)   ' Execute search
        For Each matT In mcT   ' Traverse results
            Debug.Print matT   ' Output result
        Next
    End With
End Sub

Output in Immediate Window:

code.vba

 123
 abc53
 59wt

The regex "\s.+?\s" uses +? (lazy matching)—matches minimal characters between spaces, resulting in 3 substrings separated by spaces.

【Python】

Enter the following content in the Python Shell window:

code.python

>>> import re
>>> a = ' 123  abc53  59wt '
>>> m = re.finditer('\s.+\s&#x27;, a)  # Greedy matching(no ?)
>>> for i in m:
    print(i.group())
 123  abc53  59wt

The regex "\s.+\s" uses + (greedy matching)—matches all characters between the first and last space, resulting in the entire string.

code.python

>>> m = re.finditer('\s.+?\s&#x27;, a)  # Lazy matching(+?)
>>> for i in m:
    print(i.group())
 123
 abc53
 59wt

The regex "\s.+?\s" uses +? (lazy matching)—matches minimal characters between spaces, resulting in 3 substrings.

Table of Contents

Metacharacters

Repetition

Character Classes

Capturing Groups and Non-Capturing Groups

Zero-Width Assertions

Negative Zero-Width Assertions

Greediness vs. Laziness